New TikTok users sign up with their emails and
each user receives a text confirmation to activate their account. Assume you
are given the below tables about emails and texts.
Write a query to display the ids of the users who
did not confirm on the first day of sign-up, but confirmed on the second day.
Assumption:
action_date is the date when the user activated
their account and confirmed their sign-up through the text.
table name: emails
table name: texts
Solution:
select e.user_id from emails as e
join texts as t on e.email_id = t.email_id
where t.signup_action = 'Confirmed' and t.action_date = DateAdd(day,1,e.signup_date)
Output:
SQL Script:
CREATE TABLE [dbo].[emails](
[email_id]
[int] NOT NULL,
[user_id]
[int] NULL,
[signup_date]
[datetime] NULL,
CONSTRAINT [PK_emails] PRIMARY KEY CLUSTERED
(
[email_id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[texts] Script Date: 27-03-2023 14:26:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[texts](
[text_id]
[int] NULL,
[email_id]
[int] NULL,
[signup_action]
[nvarchar](50) NULL,
[action_date]
[datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[emails] ([email_id], [user_id], [signup_date]) VALUES (125, 7771, CAST(N'2022-06-14T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[emails] ([email_id], [user_id], [signup_date]) VALUES (433, 1052, CAST(N'2022-07-09T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[texts] ([text_id], [email_id], [signup_action], [action_date]) VALUES (6878, 125, N'Confirmed', CAST(N'2022-06-14T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[texts] ([text_id], [email_id], [signup_action], [action_date]) VALUES (6997, 433, N'Not Confirmed', CAST(N'2022-07-09T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[texts] ([text_id], [email_id], [signup_action], [action_date]) VALUES (7000, 433, N'Confirmed', CAST(N'2022-07-10T00:00:00.000' AS DateTime))
GO